CREATE TABLE [dbo].[tb_Edificios]
(
[idEdificio] [int] NOT NULL IDENTITY(1, 1),
[Nombre] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CUI] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Domicilio] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CallesReferencia] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CodPostal] [int] NULL,
[Dominio] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Localidad] [int] NULL,
[Barrio] [int] NULL,
[FechaConstruccion] [datetime] NULL,
[Superficie] [int] NULL,
[SuperficieLibre] [int] NULL,
[SuperficieAulas] [int] NULL,
[SuperficieCubierta] [int] NULL,
[Responsable] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TelResponsable] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TituloPropiedad] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinalidadOriginal] [int] NULL,
[Seguro] [int] NULL,
[AutoridadSanitaria] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AutoridadPolicial] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AutoridadBomberos] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EstadoGeneral] [int] NULL,
[TipoTenencia] [int] NULL,
[Plano] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DocTecnica] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zona] [int] NULL,
[Tipo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[delEdificio] ON [dbo].[tb_Edificios] 
FOR DELETE 
AS

DELETE tb_Espacios FROM tb_Espacios WHERE Edificio in (SELECT idEdificio from deleted)
DELETE tb_Intervenciones FROM tb_Intervenciones WHERE Edificio in (SELECT idEdificio from deleted)
DELETE tb_Servicios FROM tb_Servicios WHERE Edificio in (SELECT idEdificio from deleted)
DELETE tb_MueblesYEquipos FROM tb_MueblesYEquipos WHERE Edificio in (SELECT idEdificio from deleted)
DELETE tb_Insumos FROM tb_Insumos WHERE Edificio in (SELECT idEdificio from deleted)
DELETE tb_Instalaciones FROM tb_Instalaciones WHERE Edificio in (SELECT idEdificio from deleted)
DELETE tb_Reparaciones FROM tb_Reparaciones WHERE Edificio in (SELECT idEdificio from deleted)
DELETE tb_Inspecciones FROM tb_Inspecciones WHERE Edificio in (SELECT idEdificio from deleted)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updatetb_Edificios] ON [dbo].[tb_Edificios]
 FOR UPDATE AS 
 IF (update(idEdificio)) begin 
	 SELECT * FROM inserted 
	 /*IF @@ROWCOUNT = 1 begin 
		 UPDATE tb_Intervenciones
		 SET Edificio = (SELECT idEdificio FROM inserted)
		 WHERE tb_Intervenciones.Edificio = (SELECT idEdificio FROM deleted)
		 UPDATE tb_Instalaciones
		 SET Edificio = (SELECT idEdificio FROM inserted)
		 WHERE tb_Instalaciones.Edificio = (SELECT idEdificio FROM deleted)
		 UPDATE tb_Reparaciones
		 SET Edificio = (SELECT idEdificio FROM inserted)
		 WHERE tb_Reparaciones.Edificio = (SELECT idEdificio FROM deleted)
		 UPDATE tb_Inspecciones
		 SET Edificio = (SELECT idEdificio FROM inserted)
		 WHERE tb_Inspecciones.Edificio = (SELECT idEdificio FROM deleted)
		 UPDATE tb_Servicios
		 SET Edificio = (SELECT idEdificio FROM inserted)
		 WHERE tb_Servicios.Edificio = (SELECT idEdificio FROM deleted)
		 UPDATE tb_RelEdificioOrg
		 SET Edificio = (SELECT idEdificio FROM inserted)
		 WHERE tb_RelEdificioOrg.Edificio = (SELECT idEdificio FROM deleted)
		 UPDATE tb_Insumos
		 SET Edificio = (SELECT idEdificio FROM inserted)
		 WHERE tb_Insumos.Edificio = (SELECT idEdificio FROM deleted)
		 UPDATE tb_Espacios
		 SET Edificio = (SELECT idEdificio FROM inserted)
		 WHERE tb_Espacios.Edificio = (SELECT idEdificio FROM deleted)
		 UPDATE tb_MueblesYEquipos
		 SET Edificio = (SELECT idEdificio FROM inserted)
		 WHERE tb_MueblesYEquipos.Edificio = (SELECT idEdificio FROM deleted)
	end  ELSE begin 
		 ROLLBACK TRANSACTION 
 end 
*/
 end
GO
ALTER TABLE [dbo].[tb_Edificios] ADD CONSTRAINT [PK_tb_Edificios] PRIMARY KEY NONCLUSTERED  ([idEdificio]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Edificios] WITH NOCHECK ADD CONSTRAINT [FK_tb_Edificios_tb_barrios] FOREIGN KEY ([Barrio]) REFERENCES [dbo].[tb_barrios] ([idBarrio])
GO
ALTER TABLE [dbo].[tb_Edificios] WITH NOCHECK ADD CONSTRAINT [FK_tb_Edificios_tb_Estados] FOREIGN KEY ([EstadoGeneral]) REFERENCES [dbo].[tb_Estados] ([idEstado])
GO
ALTER TABLE [dbo].[tb_Edificios] WITH NOCHECK ADD CONSTRAINT [FK_tb_Edificios_tb_TiposDeFinalidades] FOREIGN KEY ([FinalidadOriginal]) REFERENCES [dbo].[tb_TiposDeFinalidades] ([idTipoDeFinalidad])
GO
ALTER TABLE [dbo].[tb_Edificios] WITH NOCHECK ADD CONSTRAINT [FK_tb_Edificios_tb_Localidades] FOREIGN KEY ([Localidad]) REFERENCES [dbo].[tb_Localidades] ([idLocalidad])
GO
ALTER TABLE [dbo].[tb_Edificios] WITH NOCHECK ADD CONSTRAINT [FK_tb_Edificios_tb_Seguros] FOREIGN KEY ([Seguro]) REFERENCES [dbo].[tb_Seguros] ([idSeguro])
GO
ALTER TABLE [dbo].[tb_Edificios] WITH NOCHECK ADD CONSTRAINT [FK_tb_Edificios_tb_TiposDeTenencia] FOREIGN KEY ([TipoTenencia]) REFERENCES [dbo].[tb_TiposDeTenencia] ([idTipoDeTenencia])
GO
ALTER TABLE [dbo].[tb_Edificios] WITH NOCHECK ADD CONSTRAINT [FK_tb_edificios_tb_zonas] FOREIGN KEY ([zona]) REFERENCES [dbo].[tb_Zonas] ([idZona])
GO
